﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using NLBS.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace NLBS.Data.Install
{
   public class PjBlogDataImport
    {
       private OleDbConnection _dataSouce;

       public OleDbConnection AccessConn
        {
            get { return _dataSouce; }
            set { _dataSouce = value; }
        }

       private string _sqldataSouce;

       public string SqlConnectionString
       {
           get { return _sqldataSouce; }
           set { _sqldataSouce = value; }
       }

       private string _oldedbdataSouceString;

       public string OldDbConnectionString
       {
           get { return _oldedbdataSouceString; }
           set { _oldedbdataSouceString = value; }
       }

       public PjBlogDataImport()
       {
           string path = HttpContext.Current.Server.MapPath("App_Data/PBLog22.mdb");
           _sqldataSouce=ConfigurationManager.ConnectionStrings["NLBSConnectionString"].ConnectionString;
           _dataSouce = CteateOleDbSource(path);
           _oldedbdataSouceString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "data source={0}", path);
       }


       public PjBlogDataImport(string oledbPath, string conn)
       {
           
           //Data Source=60.169.2.179;Initial Catalog=sq_zxh;uid=zxh;password=64957997
           
           //_dataSouce = CteateOleDbSource(oledbPath);
           _oldedbdataSouceString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "data source={0}", oledbPath);
           _accessDb = new GenericDatabase(_oldedbdataSouceString,System.Data.OleDb.OleDbFactory.Instance);
           
           _sqlDb = new SqlDatabase(conn);
           
       }


       public OleDbConnection CteateOleDbSource(string path)
       {
           string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                             "data source=" + path;

           OleDbConnection conn = new OleDbConnection(connStr);
           return conn;
       }

       private GenericDatabase _accessDb;

       public GenericDatabase AccessDb
       {
           get { return _accessDb; }
           
       }

       private SqlDatabase _sqlDb;

       public SqlDatabase SqlDb
       {
           get { return _sqlDb; }
         
       }

       public void TranSmiles()
       {
           DbCommand dbCommand = AccessDb.GetSqlStringCommand("select * from blog_Smilies");


           using (IDataReader dataReader = AccessDb.ExecuteReader(dbCommand))
           {
               while (dataReader.Read())
               {
                   int id = Convert.ToInt32(dataReader["sm_ID"].ToString());
                   string image = dataReader["sm_image"].ToString();
                   string text = dataReader["sm_text"].ToString();
                   DbCommand insertCommand = SqlDb.GetSqlStringCommand(string.Format(@"
                   SET IDENTITY_INSERT blog_smilies on;
                   insert into blog_smilies (sm_id,sm_image,sm_code) values({0},'{1}','{2}')", id, image, text));
                   SqlDb.ExecuteNonQuery(insertCommand);
               }
           }
       }

       public void TranCategory()
       {
           DbCommand dbCommand = AccessDb.GetSqlStringCommand("select * from blog_category");


           using (IDataReader dataReader = AccessDb.ExecuteReader(dbCommand))
           {
               while (dataReader.Read())
               {
                   if (string.IsNullOrEmpty(dataReader["cate_url"].ToString()))
                   {
                       int cate_id = Convert.ToInt32(dataReader["cate_id"].ToString());
                       string cate_name = dataReader["cate_name"].ToString();
                       int cate_order = Convert.ToInt32(dataReader["cate_order"].ToString());
                       bool cate_lock = Convert.ToBoolean(dataReader["cate_lock"].ToString());
                       int cate_count = Convert.ToInt32(dataReader["cate_count"].ToString());
                       DbCommand insertCommand = SqlDb.GetSqlStringCommand(string.Format(@"
            SET IDENTITY_INSERT blog_Category on;
            INSERT INTO [dbo].[blog_Category] 
            ([cat_id],[cat_name], [cat_order], [cat_articleCount], [cat_hidden], [cat_locked]) 
            VALUES 
            ({0},'{1}',{2}, {3},{4},{5});
            SET IDENTITY_INSERT blog_Category off;", cate_id, cate_name, cate_order, cate_count, 1, BooleanToInt(cate_lock)));
                       SqlDb.ExecuteNonQuery(insertCommand);
                   }
               }
           }
       }
   


       public void TranComment()
       {
           DbCommand dbCommand = AccessDb.GetSqlStringCommand("select * from blog_comment");


           using (IDataReader dataReader = AccessDb.ExecuteReader(dbCommand))
           {
               while (dataReader.Read())
               {

                   DbCommand insertCommand = SqlDb.GetSqlStringCommand(@"
            SET IDENTITY_INSERT blog_Comment on;
            INSERT INTO [blog_Comment] 
            ([comm_id],[log_id], [comm_content], [comm_authorID], [comm_author], 
            [comm_editMark], [comm_hidden], [comm_ubbFlags], [comm_postTime], [comm_ip]) 
            VALUES (@comm_id,@logID, @content, @authorID, @author, @editMark, @hidden, @ubbFlags, @postTime, @ip);
            SET IDENTITY_INSERT blog_Comment off");
                   SqlDb.AddInParameter(insertCommand, "comm_id", DbType.Int32, Convert.ToInt32(dataReader["comm_id"].ToString()));
                   SqlDb.AddInParameter(insertCommand, "logID", DbType.Int32, Convert.ToInt32(dataReader["blog_id"].ToString()));
                   SqlDb.AddInParameter(insertCommand, "content", DbType.String, dataReader["comm_content"].ToString());
                   SqlDb.AddInParameter(insertCommand, "authorID", DbType.Int32, 0);
                   SqlDb.AddInParameter(insertCommand, "author", DbType.String, dataReader["comm_author"].ToString());
                   SqlDb.AddInParameter(insertCommand, "editMark", DbType.String, string.Empty);
                   SqlDb.AddInParameter(insertCommand, "hidden", DbType.Boolean, false);
                   SqlDb.AddInParameter(insertCommand, "ubbFlags", DbType.Int32, "111111");
                   SqlDb.AddInParameter(insertCommand, "postTime", DbType.DateTime, Convert.ToDateTime(dataReader["comm_posttime"].ToString()));
                   SqlDb.AddInParameter(insertCommand, "ip", DbType.String, dataReader["comm_postip"].ToString());
                   SqlDb.ExecuteNonQuery(insertCommand);
               }
           }
       }

       public void TranGuestBook()
       {
           DbCommand bookCommand = AccessDb.GetSqlStringCommand("select * from blog_book");

           Database db = SqlDb;
           using (IDataReader dr = AccessDb.ExecuteReader(bookCommand))
           {
               while (dr.Read())
               {

                   DateTime replytime=DateTime.Now;
                   if (string.IsNullOrEmpty(dr["book_replyTime"].ToString()))
                   {
                       replytime = Convert.ToDateTime(dr["book_postTime"].ToString());
                   }
                   else
                   {
                       replytime = Convert.ToDateTime(dr["book_replyTime"].ToString());
                   }
                   DbCommand dbCommand = db.GetSqlStringCommand(@"
            SET IDENTITY_INSERT Guestbook on;
            INSERT INTO [Guestbook] 
            ([gb_id],[gb_username], [gb_userID], 
            [gb_content], [gb_editMark], [gb_ubbFlags], [gb_postTime], [gb_replyUsername], 
            [gb_reply], [gb_replyTime], [gb_hidden], [gb_ip]) VALUES 
            (@gbId,@username, @userID, @content, @editMark, @ubbFlags, @postTime, 
            @replyUsername, @reply, @replyTime, @hidden, @ip)
            SET IDENTITY_INSERT Guestbook off");
                   db.AddInParameter(dbCommand, "gbId", DbType.String, int.Parse(dr["book_id"].ToString()));
                   db.AddInParameter(dbCommand, "userName", DbType.String,dr["book_messager"].ToString());
                   db.AddInParameter(dbCommand, "userID", DbType.Int32, 0);
                   db.AddInParameter(dbCommand, "content", DbType.String, dr["book_content"].ToString());
                   db.AddInParameter(dbCommand, "editMark", DbType.String, string.Empty);
                   db.AddInParameter(dbCommand, "ubbFlags", DbType.String, "111111");
                   db.AddInParameter(dbCommand, "postTime", DbType.DateTime, Convert.ToDateTime(dr["book_postTime"].ToString()));
                   db.AddInParameter(dbCommand, "replyUserName", DbType.String, dr["book_replyAuthor"].ToString());
                   db.AddInParameter(dbCommand, "reply", DbType.String,string.IsNullOrEmpty(dr["book_reply"].ToString()) ? string.Empty : dr["book_reply"].ToString());
                   db.AddInParameter(dbCommand, "replyTime", DbType.DateTime, replytime);
                   db.AddInParameter(dbCommand, "hidden", DbType.Boolean, Convert.ToBoolean(dr["book_HiddenReply"].ToString()));
                   db.AddInParameter(dbCommand, "ip", DbType.String, dr["book_ip"].ToString());
                   SqlDb.ExecuteNonQuery(dbCommand);
               }
           }
       }

       public void TranUser()
       {
           DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select * from blog_member");

           Database db = SqlDb;
           using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
           {
               while (dr.Read())
               {

                   DbCommand dbCommand = db.GetSqlStringCommand(@"
            SET IDENTITY_INSERT blog_User on;
            INSERT INTO [blog_User] 
            ([user_id],[user_name], [user_password], 
            [user_salt], [user_groupID], [user_gender], [user_email], 
            [user_hideEmail], [user_homepage], [user_articleCount], 
            [user_commentCount], [user_lastVisit], [user_ip],[user_hashKey]) 
            VALUES 
            (@user_id,@user_name, @user_password, @user_salt, @user_groupID, @user_gender, 
            @user_email, @user_hideEmail, @user_homepage, 
            @user_articleCount, @user_commentCount, @user_lastVisit, @user_ip,@user_hashKey);
            SET IDENTITY_INSERT blog_User off;");
                   db.AddInParameter(dbCommand, "user_id", DbType.Int32, Convert.ToInt32(dr["mem_id"].ToString()));
                   db.AddInParameter(dbCommand, "user_name", DbType.String, dr["mem_name"].ToString());
                   db.AddInParameter(dbCommand, "user_password", DbType.String, dr["mem_password"].ToString());
                   db.AddInParameter(dbCommand, "user_salt", DbType.Int32, 1);
                   string mem_Status=dr["mem_Status"].ToString();
                   int group = 3;
                   if (mem_Status == "SupAdmin")
                   {
                       group = 1;
                   }
                   db.AddInParameter(dbCommand, "user_groupID", DbType.Int32, group);
                   db.AddInParameter(dbCommand, "user_gender", DbType.Int32, Convert.ToInt32(dr["mem_sex"].ToString()));
                   db.AddInParameter(dbCommand, "user_email", DbType.String, dr["mem_email"].ToString());
                   db.AddInParameter(dbCommand, "user_hideEmail", DbType.Boolean, Convert.ToBoolean(dr["mem_hideemail"].ToString()));
                   db.AddInParameter(dbCommand, "user_homepage", DbType.String, dr["mem_homepage"].ToString());
                   db.AddInParameter(dbCommand, "user_articleCount", DbType.Int32,Convert.ToInt32(dr["mem_PostLogs"].ToString()));
                   db.AddInParameter(dbCommand, "user_commentCount", DbType.Int32, Convert.ToInt32(dr["mem_PostComms"].ToString()));
                   db.AddInParameter(dbCommand, "user_lastVisit", DbType.DateTime, dr["mem_lastvisit"].ToString());
                   db.AddInParameter(dbCommand, "user_ip", DbType.String, dr["mem_lastip"].ToString());
                   db.AddInParameter(dbCommand, "user_hashKey", DbType.String, dr["mem_hashKey"].ToString());
                   db.ExecuteNonQuery(dbCommand);
               }
           }
       }

       public void TranTrackBack()
       {
           DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select * from blog_trackback");

           Database db = SqlDb;
           using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
           {
               while (dr.Read())
               {
                   DbCommand dbCommand = db.GetSqlStringCommand(@"
           SET IDENTITY_INSERT blog_Trackback on;
           INSERT INTO [blog_Trackback] 
           ([tb_id],[log_id], [tb_url], [tb_title], [tb_blog], [tb_excerpt], [tb_time], [tb_ip]) 
           VALUES (@tb_id,@log_id, @tb_url, @tb_title, @tb_blog, @tb_excerpt, @tb_time, @tb_ip)
           SET IDENTITY_INSERT blog_Trackback off;");

                   db.AddInParameter(dbCommand, "tb_id", DbType.Int32, Convert.ToInt32(dr["tb_id"].ToString()));
                   db.AddInParameter(dbCommand, "log_id", DbType.Int32, Convert.ToInt32(dr["blog_id"].ToString()));
                   db.AddInParameter(dbCommand, "tb_url", DbType.String, dr["tb_url"].ToString());
                   db.AddInParameter(dbCommand, "tb_title", DbType.String, dr["tb_title"].ToString());
                   db.AddInParameter(dbCommand, "tb_blog", DbType.String, dr["tb_site"].ToString());
                   db.AddInParameter(dbCommand, "tb_excerpt", DbType.String, dr["tb_Intro"].ToString());
                   db.AddInParameter(dbCommand, "tb_time", DbType.DateTime,Convert.ToDateTime(dr["tb_posttime"].ToString()));
                   db.AddInParameter(dbCommand, "tb_ip", DbType.String, string.Empty);
                   db.ExecuteNonQuery(dbCommand);
               }
           }
       }

       public void TranUserGroup()
       {

       }

       public void TranWidget()
       {

       }

       public void TranArticle()
       {
            DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select * from blog_content");

            Database db = this.SqlDb;
            using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
            {
                while (dr.Read())
                {

                    DbCommand dbCommand = db.GetSqlStringCommand(@"
             SET IDENTITY_INSERT blog_Article on;
             INSERT INTO [blog_Article] 
            ([log_id],[log_catID], [log_title], [log_authorID], [log_author], [log_editMark],
             [log_trackbackURL], [log_content0], [log_content1], [log_mode], [log_locked],
              [log_selected], [log_ubbFlags], [log_postTime], [log_ip], 
              [log_commentCount], [log_viewCount], [log_trackbackCount],log_istop,log_enablerss) 
              VALUES 
              (@logID,@catID, @title, @authorID, @author, @editMark, 
              @trackbackURL, @content0, @content1, @mode, @locked, 
              @selected, @ubbFlags, @postTime, @ip, 
              @commentCount, @viewCount, @trackbackCount,@istop,@enableInRss)
              SET IDENTITY_INSERT blog_Article off;");

                    string editAuthor = string.Empty;
                    string logmark = dr["log_Modify"].ToString();
                    if (logmark.Length > 0)
                    {
                        int startAuthor = logmark.IndexOf(" ");
                        int endAuthor = logmark.IndexOf(" 于");
                        int time = logmark.IndexOf("编辑");
                        string Author = logmark.Substring(startAuthor, endAuthor-startAuthor);
                        string editTime = logmark.Substring(endAuthor+2, time - endAuthor);
                        //Admin$|$2008-01-09 09:17:51
                        editAuthor = Author+"$|$" + editTime;
                    }
                    else
                    {
                        editAuthor = logmark;
                    }

                    db.AddInParameter(dbCommand, "logID", DbType.Int32, Convert.ToInt32(dr["log_id"].ToString()));
                    db.AddInParameter(dbCommand, "catID", DbType.Int32, Convert.ToInt32(dr["log_cateid"].ToString()));
                    db.AddInParameter(dbCommand, "title", DbType.String, dr["log_title"].ToString());
                    db.AddInParameter(dbCommand, "authorID", DbType.Int32, 0);
                    db.AddInParameter(dbCommand, "author", DbType.String, dr["log_author"].ToString());
                    db.AddInParameter(dbCommand, "editMark", DbType.String, editAuthor);

                    db.AddInParameter(dbCommand, "trackbackURL", DbType.String, dr["log_FromUrl"].ToString());
                    db.AddInParameter(dbCommand, "content0", DbType.String, dr["log_Intro"].ToString());
                    db.AddInParameter(dbCommand, "content1", DbType.String, dr["log_Content"].ToString());
                    db.AddInParameter(dbCommand, "mode", DbType.Int16, 1);
                    db.AddInParameter(dbCommand, "locked", DbType.Boolean, Convert.ToBoolean(dr["log_DisComment"].ToString()));

                    db.AddInParameter(dbCommand, "selected", DbType.Boolean, false);
                    db.AddInParameter(dbCommand, "ubbFlags", DbType.String, dr["log_ubbFlags"].ToString());
                    db.AddInParameter(dbCommand, "postTime", DbType.DateTime,Convert.ToDateTime(dr["log_PostTime"].ToString()));
                    db.AddInParameter(dbCommand, "ip", DbType.String, string.Empty);
                    db.AddInParameter(dbCommand, "commentCount", DbType.Int32, Convert.ToInt32(dr["log_CommNums"].ToString()));
                    db.AddInParameter(dbCommand, "viewCount", DbType.Int32,Convert.ToInt32(dr["log_ViewNums"].ToString()));
                    db.AddInParameter(dbCommand, "trackbackCount", DbType.Int32, Convert.ToInt32(dr["log_QuoteNums"].ToString()));
                    db.AddInParameter(dbCommand, "istop", DbType.Boolean, Convert.ToBoolean(dr["log_isTop"].ToString()));
                    db.AddInParameter(dbCommand, "enableInRss", DbType.Boolean, true);
                    
                    db.ExecuteNonQuery(dbCommand);
                }
            }
       }

       public void TranWordFilter()
       {
       }



       private List<Tag> GetTagList()
       {

           DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select tag_id,tag_Name from blog_tag");

           Database db = this.SqlDb;
           List<Tag> tags = new List<Tag>();
           using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
           {
               while (dr.Read())
               {
                   Tag tag = new Tag();
                   tag.TagID =Convert.ToInt32(dr["tag_id"].ToString());
                   tag.Name = dr["tag_Name"].ToString();
                   tags.Add(tag);
               }
               return tags;
           }
          
       }

       public void TranTag()
       {



           DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select log_id,log_tag from blog_content");

            Database db = this.SqlDb;
            List<Tag> tagList = GetTagList();
            using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
            {
                while (dr.Read())
                {
                    string logtag = dr["log_tag"].ToString();
                    int logID=Convert.ToInt32(dr["log_id"].ToString());
                    if (logtag.Length > 0)
                    {
                        string[] tags = logtag.Split(new char[] { '}' });
                        foreach (string item in tags)
                        {
                            if (item.Length > 0)
                            {
                                string itemTag = item.Substring(1);
                                Tag findTag = tagList.Find(delegate(Tag tag)
                                {
                                    return tag.TagID == Convert.ToInt32(itemTag);
                                });
                                DbCommand comm = db.GetSqlStringCommand(string.Format(
                                "insert into blog_tag (log_id,tag_Name) values({0},'{1}')", logID, findTag.Name));
                                db.ExecuteNonQuery(comm);
                            }
                        }
                    }
                }
            }
       }



       public void TranInfo()
       {
           DbCommand selectdbCommand = AccessDb.GetSqlStringCommand("select * from blog_Info");

           
           StringBuilder sb=new StringBuilder();
           
           
           Database db = this.SqlDb;
           using (IDataReader dr = AccessDb.ExecuteReader(selectdbCommand))
           {
               if(dr.Read())
               {
                   sb.AppendFormat("update set set_value1='{0}' where set_name='blogTitle';", dr["blog_Title"].ToString());
                   sb.AppendFormat("update set set_value1='{0}' where set_name='baseURL';", dr["blog_URL"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='counterArticle';", dr["blog_LogNums"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='counterComment';", dr["blog_CommNums"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='counterVisitor';", dr["blog_VisitNums"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='counterTrackback';", dr["blog_tbNums"].ToString());

                   sb.AppendFormat("update set set_value0={0} where set_name='articlePerPageNormal';", dr["blog_PerPage"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='commentPerPage';", dr["blog_commPage"].ToString());
                   

                   sb.AppendFormat("update set set_value1='{0}' where set_name='blogWebMaster';", dr["blog_master"].ToString());
                   sb.AppendFormat("update set set_value1='{0}' where set_name='blogWebMasterEmail';", dr["blog_email"].ToString());

                   sb.AppendFormat("update set set_value0={0} where set_name='minPostDuration';", dr["blog_commTimerout"].ToString());
                   sb.AppendFormat("update set set_value0={0} where set_name='enableRegister';", dr["blog_Disregister"].ToString());

                   DbCommand dbCommand = db.GetSqlStringCommand(sb.ToString());
                   db.ExecuteNonQuery(dbCommand);
               }
           }
       }

       private int BooleanToInt(bool obj)
       {
           if (obj)
           {
               return 1;
           }
           return 0;
       }
    }
}
